﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanhTu.GNC.Media.KQXS.DataAccess.DO;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNC.Media.KQXS.Common;

namespace ThanhTu.GNC.Media.KQXS.DataAccess.DAL
{
    public class BreakingNewsDAL : BaseDAL
    {
        public BreakingNewsDAL() : base() { }

        public int SaveNews(BreakingNewsDO vBreakingNewsDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.SAVENEWS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INCONTENT", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Content;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INIMAGE", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Image;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INGroupID", OracleDbType.Int32);
                oraParam.Value = vBreakingNewsDO.Groupnews;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INTitle", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Title;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int UpdateNews(BreakingNewsDO vBreakingNewsDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.UPDATENEWS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INNEWSID", OracleDbType.Int32);
                oraParam.Value = vBreakingNewsDO.Newid;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INCONTENT", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Content;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INIMAGE", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Image;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INGroupID", OracleDbType.Int32);
                oraParam.Value = vBreakingNewsDO.Groupnews;
                oraComm.Parameters.Add(oraParam);


                oraParam = new OracleParameter("INTitle", OracleDbType.Varchar2);
                oraParam.Value = vBreakingNewsDO.Title;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int DeleteNews(int NewID)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.DELETENEWS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INNEWSID", OracleDbType.Int32);
                oraParam.Value = NewID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
                { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public BreakingNewsDO getbyNews(int NewID)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.GETBYNEWS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INNEWSID", OracleDbType.Int32);
                oraParam.Value = NewID;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataRow dr = null;
                    BreakingNewsDO vBreakingNewsDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                dr = ds.Tables[0].Rows[0];
                                vBreakingNewsDO = new BreakingNewsDO();

                                if (!Convert.IsDBNull(dr["NEWSID"]))
                                { vBreakingNewsDO.Newid = Convert.ToInt32(dr["NEWSID"]); }
                                if (!Convert.IsDBNull(dr["IMAGE"]))
                                { vBreakingNewsDO.Image = Convert.ToString(dr["IMAGE"]); }
                                if (!Convert.IsDBNull(dr["GROUPID"]))
                                { vBreakingNewsDO.Groupnews = Convert.ToInt32(dr["GROUPID"]); }
                                if (!Convert.IsDBNull(dr["NEWSCONTENT"]))
                                { vBreakingNewsDO.Content = Utils.ToUTF8String((byte[])dr["NEWSCONTENT"]); }
                                dr = null;
                            }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return vBreakingNewsDO;
                }
            }
        }
        public List<BreakingNewsDO> SelectAll()
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.GETALLNEWS";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);


                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<BreakingNewsDO> vBreakingNewsDOCollection = null;
                    DataRow dr = null;
                    BreakingNewsDO vBreakingNewsDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vBreakingNewsDOCollection = new List<BreakingNewsDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vBreakingNewsDO = new BreakingNewsDO();

                                if (!Convert.IsDBNull(dr["NEWSID"]))
                                { vBreakingNewsDO.Newid = int.Parse(Convert.ToString(dr["NEWSID"])); }

                                if (!Convert.IsDBNull(dr["IMAGE"]))
                                { vBreakingNewsDO.Image = Convert.ToString(dr["IMAGE"]); }

                                if (!Convert.IsDBNull(dr["TITLE"]))
                                { vBreakingNewsDO.Title = Convert.ToString(dr["TITLE"]); }

                                if (!Convert.IsDBNull(dr["GROUPID"]))
                                { vBreakingNewsDO.Groupnews = Convert.ToInt32(dr["GROUPID"]); }

                                if (!Convert.IsDBNull(dr["NEWSCONTENT"]))
                                { vBreakingNewsDO.Content = Utils.ToUTF8String((byte[])dr["NEWSCONTENT"]); }


                                vBreakingNewsDOCollection.Add(vBreakingNewsDO);
                                dr = null;
                                vBreakingNewsDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;

                    return vBreakingNewsDOCollection;
                }
            }
        }
        public DataTable selectAll_Page(int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.GETALLNEWSPAGE";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
        public DataTable selectAll_groupnews(int groupid, int pageIndex, int pageSize, ref int rowCount)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "BREAKINGNEWS_PKG.GetAllNewsGroup";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("INGroupID", OracleDbType.Int32);
                oraParam.Value = groupid;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
                oraParam.Value = pageIndex;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
                oraParam.Value = pageSize;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];

                            if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                            { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                        }

                        ds.Dispose();
                    }

                    oraComm.Dispose();
                    oraParam = null;

                    return dt;
                }
            }
        }
    }
}
